﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.ComponentModel;
using System.Data;
namespace HiStaff.Dal
{
    public class DalUtility
    {
        private const string ENTITY_CLASSNAME = "Entity";
        private const string ORALCE_PACKAGE_SYSTEM = "PKG_SYSTEM";
        private const string ORALCE_PRS_USERARGS = "prs_userargs_from_object";
        private const string ORALCE_PRS_USERARGS_ARG1 = "P_PACKAGE_NAME";
        private const string ORALCE_PRS_USERARGS_ARG2 = "P_OBJECT_NAME";
        private const string ORALCE_PRS_USERARGS_ARG3 = "P_CUR";

        public const string ORACLE_CURSOR_TYPE = "REF CURSOR";
        private const string ORACLE_DIRECTION_IN = "IN";
        private const string ORACLE_DIRECTION_OUT = "OUT";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static OracleType GetOracleType(string datatype)
        {
            switch (datatype)
            {
                case "NVARCHAR2": return OracleType.NVarChar;
                case "NUMBER": return OracleType.Number;
                case "CLOB": return OracleType.Clob;
                case "NCLOB": return OracleType.NClob;
                case "CHAR": return OracleType.Char;
                case "DATE": return OracleType.DateTime;
                case "VARCHAR2": return OracleType.VarChar;
                case "BLOB": return OracleType.Blob;
                default: return OracleType.NVarChar;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static DbType GetDbType(string datatype)
        {
            switch (datatype)
            {
                case "NVARCHAR2": return DbType.String;
                case "CLOB": return DbType.String;
                case "NUMBER": return DbType.Decimal;
                case "DATE": return DbType.DateTime;
                case "BLOB": return DbType.Binary;
                default: return DbType.String;
            }
        }

        ///// <summary>
        ///// 
        ///// </summary>
        ///// <param name="o"></param>
        ///// <returns></returns>
        //public static EnumOraDbType GetEnumOraDbType(object o)
        //{
        //    if (o is string) return EnumOraDbType.Varchar2;
        //    if (o is DateTime) return EnumOraDbType.Date;
        //    if (o is Int64) return EnumOraDbType.Int64;
        //    if (o is Int32) return EnumOraDbType.Int32;
        //    if (o is Int16) return EnumOraDbType.Int16;
        //    if (o is byte) return EnumOraDbType.Byte;
        //    if (o is decimal) return EnumOraDbType.Decimal;
        //    if (o is float) return EnumOraDbType.Single;
        //    if (o is double) return EnumOraDbType.Double;
        //    if (o is byte[]) return EnumOraDbType.Blob;
        //    return EnumOraDbType.Varchar2;
        //}

        /// <summary>
        /// Parse and get returned object from data reader
        ///  DataReader => Object mapping function
        ///  Now recursive for propertys that are classes and inheritate from Entite
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameters"></param>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static bool LoadObjectFromDataReader(object theInstanceType, IDataReader reader)
        {
            bool propertiesChanged = false;
            PropertyDescriptorCollection infos = TypeDescriptor.GetProperties(theInstanceType.GetType());

            //if (infos != null && infos.Count > 0)
            if (infos != null)
            {
                foreach (PropertyDescriptor info in infos)
                {
                    for (int f = 0; f < reader.FieldCount; f++)
                    {
                        string fName = reader.GetName(f);
                        string miName = info.Name;

                        if (miName.ToLower() == fName.ToLower())
                        {
                            object value = reader.GetValue(f);
                            if (value != DBNull.Value)
                            {
                                object obj = value;
                                TypeConverter typeConverter = info.Converter;
                                if (typeConverter != null)
                                {
                                    if (value.GetType().Name == "Byte[]")
                                    {
                                        obj = value;
                                    }
                                    else
                                        obj = typeConverter.ConvertFromString(value.ToString());
                                }
                                info.SetValue(theInstanceType, obj);
                                propertiesChanged = true;
                            }
                            break;
                        }
                    }
                }
            }
            // So we can know if at least one property of the object is set
            return propertiesChanged;
        }

        /// <summary>
        /// Creates an object from the specified type and calls the DataReader => Object mapping function
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameters"></param>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static T LoadObjectFromDataReader<T>(IDataReader reader) where T : new()
        {
            Type t = typeof(T);
            PropertyDescriptorCollection infos = TypeDescriptor.GetProperties(t);
            object theInstanceType = new T();
            LoadObjectFromDataReader(theInstanceType, reader);
            return (T)theInstanceType;
        }
        /// <summary>
        /// Get returned list objects from database based on input criteria
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public static List<T> LoadObjectListFromDatabase<T>(OracleCommand command) where T : new()
        {
            List<T> listT = new List<T>();

            //data reader

            //Log.Instance.writeLog("Line 138-Connection State:" + command.Connection.State.ToString());

            IDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                listT.Add((T)LoadObjectFromDataReader<T>(reader));
            }
            reader.Close();
            return listT;
        }

        /// <summary>
        /// Get returned object from database based on input criteria
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public static T LoadObjectFromDatabase<T>(OracleCommand command) where T : new()
        {
            bool isRead = false;
            T objT = new T();

            //data reader
            //Log.Instance.writeLog("Connection State:" + command.Connection.ConnectionString);
            //Log.Instance.writeLog("Connection State:" + command.Connection.State.ToString());

            IDataReader reader = command.ExecuteReader();
            isRead = reader.Read();

            if (isRead)
            {
                objT = (T)LoadObjectFromDataReader<T>(reader);

                reader.Close();
                return objT;
            }
            else
            {
                reader.Close();
                return default(T);
            }
        }

        public static List<USER_ARGUMENTS> SelectUserArgs(string packname, string procname)
        {
            OracleParameter param;
            OracleConnection conn = DBConnection.GetConnection();
            OracleCommand command = conn.CreateCommand();
            command.CommandText = ORALCE_PACKAGE_SYSTEM + "." + ORALCE_PRS_USERARGS;
            command.CommandType = CommandType.StoredProcedure;

            //Log.Instance.writeLog("Line 178-Connection State:" + command.Connection.State.ToString());

            param = new OracleParameter();
            param.ParameterName = ORALCE_PRS_USERARGS_ARG1;
            param.OracleType = OracleType.NVarChar;
            param.Value = packname;
            param.Direction = ParameterDirection.Input;

            command.Parameters.Add(param);
            param = new OracleParameter();
            param.ParameterName = ORALCE_PRS_USERARGS_ARG2;
            param.OracleType = OracleType.NVarChar;
            param.Value = procname;
            param.Direction = ParameterDirection.Input;
            command.Parameters.Add(param);

            param = new OracleParameter();
            param.ParameterName = ORALCE_PRS_USERARGS_ARG3;
            param.OracleType = OracleType.Cursor;
            param.Direction = ParameterDirection.Output;
            command.Parameters.Add(param);

            return DalUtility.LoadObjectListFromDatabase<USER_ARGUMENTS>(command);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="obj"></param>
        /// <param name="lstUserArgs"></param>
        public static void LoadParametersFromObject(OracleCommand command, Object obj, List<USER_ARGUMENTS> lstUserArgs)
        {
            OracleParameter param;
            if (command == null) return;
            if (obj is DataRow)
            {
                foreach (USER_ARGUMENTS UserArg in lstUserArgs)
                {
                    if (UserArg.DATA_TYPE == ORACLE_CURSOR_TYPE)
                    {
                        param = new OracleParameter();
                        param.ParameterName = UserArg.ARGUMENT_NAME;
                        if (UserArg.IN_OUT == ORACLE_DIRECTION_IN)
                            param.Direction = ParameterDirection.Input;
                        else if (UserArg.IN_OUT == ORACLE_DIRECTION_OUT)
                            param.Direction = ParameterDirection.Output;
                        else param.Direction = ParameterDirection.InputOutput;
                        param.OracleType = OracleType.Cursor;
                        command.Parameters.Add(param);
                        continue;
                    }
                    else
                    {
                        if (UserArg.ARGUMENT_NAME != string.Empty && UserArg.POSITION > 0)
                        {
                            DataRow dr = (DataRow)obj;
                            foreach (DataColumn dc in dr.Table.Columns)
                            {
                                if (UserArg.ARGUMENT_NAME.ToUpper() == ("P_" + dc.ColumnName).ToUpper())
                                {
                                    param = new OracleParameter();
                                    param.ParameterName = UserArg.ARGUMENT_NAME;
                                    param.Value = dr[dc.ColumnName];

                                    if (UserArg.IN_OUT == ORACLE_DIRECTION_IN)
                                        param.Direction = ParameterDirection.Input;
                                    else if (UserArg.IN_OUT == ORACLE_DIRECTION_OUT)
                                        param.Direction = ParameterDirection.Output;
                                    else param.Direction = ParameterDirection.InputOutput;

                                    param.OracleType = GetOracleType(UserArg.DATA_TYPE);
                                    param.DbType = GetDbType(UserArg.DATA_TYPE);

                                    if (param.DbType == DbType.String
                                            && param.Direction != ParameterDirection.Input)
                                    {
                                        param.Size = 2000;
                                    }

                                    command.Parameters.Add(param);
                                    break;
                                }
                            }
                        }
                        else
                        {
                            param = new OracleParameter();
                            param.Direction = ParameterDirection.ReturnValue;
                            param.OracleType = GetOracleType(UserArg.DATA_TYPE);
                            command.Parameters.Add(param);
                        }
                    }

                }
            }
            else
            {
                Type t = obj.GetType();
                PropertyDescriptorCollection infos = TypeDescriptor.GetProperties(t);
                //if (infos != null && infos.Count > 0)
                if (infos != null)
                {
                    foreach (USER_ARGUMENTS UserArg in lstUserArgs)
                    {
                        if (UserArg.DATA_TYPE == ORACLE_CURSOR_TYPE)
                        {
                            param = new OracleParameter();
                            param.ParameterName = UserArg.ARGUMENT_NAME;
                            if (UserArg.IN_OUT == ORACLE_DIRECTION_IN)
                                param.Direction = ParameterDirection.Input;
                            else if (UserArg.IN_OUT == ORACLE_DIRECTION_OUT)
                                param.Direction = ParameterDirection.Output;
                            else param.Direction = ParameterDirection.InputOutput;
                            param.OracleType = OracleType.Cursor;
                            command.Parameters.Add(param);
                            continue;
                        }
                        else
                        {
                            if (UserArg.ARGUMENT_NAME != string.Empty && UserArg.POSITION > 0)
                            {
                                foreach (PropertyDescriptor info in infos)
                                {
                                    if (UserArg.ARGUMENT_NAME.ToUpper() == ("P_" + info.Name).ToUpper())
                                    {
                                        param = new OracleParameter();
                                        param.ParameterName = UserArg.ARGUMENT_NAME;
                                        param.Value = info.GetValue(obj) == null ? DBNull.Value : info.GetValue(obj);

                                        if (UserArg.IN_OUT == ORACLE_DIRECTION_IN)
                                            param.Direction = ParameterDirection.Input;
                                        else if (UserArg.IN_OUT == ORACLE_DIRECTION_OUT)
                                            param.Direction = ParameterDirection.Output;
                                        else param.Direction = ParameterDirection.InputOutput;

                                        param.OracleType = GetOracleType(UserArg.DATA_TYPE);
                                        param.DbType = GetDbType(UserArg.DATA_TYPE);

                                        if (param.DbType == DbType.String
                                                && param.Direction != ParameterDirection.Input)
                                        {
                                            param.Size = 2000;
                                        }

                                        command.Parameters.Add(param);
                                        break;
                                    }
                                    //if (info.GetValue(obj) != null){}
                                }
                            }
                            else
                            {
                                param = new OracleParameter();
                                param.Direction = ParameterDirection.ReturnValue;
                                param.OracleType = GetOracleType(UserArg.DATA_TYPE);
                                command.Parameters.Add(param);
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="obj"></param>
        public static void SetOutputValueToObject(OracleCommand command, Object obj)
        {
            if (command == null) return;
            if (obj is DataRow)
            {
                foreach (OracleParameter param in command.Parameters)
                {
                    if (param.Direction == ParameterDirection.Output ||
                        param.Direction == ParameterDirection.InputOutput ||
                        param.Direction == ParameterDirection.ReturnValue)
                    {
                        DataRow dr = (DataRow)obj;
                        foreach (DataColumn dc in dr.Table.Columns)
                        {
                            if (param.ParameterName.ToUpper() == ("P_" + dc.ColumnName).ToUpper()
                                                               && param.Value.ToString().ToUpper() != "NULL")
                            {
                                dr[dc.ColumnName] = param.Value;
                                break;
                            }
                        }
                    }
                }
            }
            else
            {
                Type t = obj.GetType();
                PropertyDescriptorCollection infos = TypeDescriptor.GetProperties(t);
                //if (infos != null && infos.Count > 0)
                if (infos != null)
                {
                    foreach (OracleParameter param in command.Parameters)
                    {
                        if (param.Direction == ParameterDirection.Output ||
                            param.Direction == ParameterDirection.InputOutput ||
                            param.Direction == ParameterDirection.ReturnValue)
                        {
                            foreach (PropertyDescriptor info in infos)
                            {
                                if (param.ParameterName.ToUpper() == ("P_" + info.Name).ToUpper()
                                    && param.Value.ToString().ToUpper() != "NULL")
                                {
                                    object objVal = param.Value;

                                    TypeConverter typeConverter = info.Converter;
                                    if (typeConverter != null)
                                    {
                                        objVal = typeConverter.ConvertFromString(objVal.ToString());
                                    }
                                    info.SetValue(obj, objVal);
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
